package cn.yuanqiao.archive.tongji.service.impl;

import cn.yuanqiao.archive.tongji.mapper.ZiDingYiTjMapper;
import cn.yuanqiao.archive.tongji.service.ZiDingYiTjService;
import cn.yuanqiao.common.utils.SecurityUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author zjj
 * @date 2023/8/15
 */
@Service
public class ZiDingYiTjServiceImpl implements ZiDingYiTjService {
    protected final Logger logger = LoggerFactory.getLogger(this.getClass());
    @Autowired
    private ZiDingYiTjMapper ziDingYiTjMapper;
    @Override
    public List<Map<String, Object>> getCategoryList() {
        String tenantCode = SecurityUtils.getTenantCode();
        List<Map<String, Object>> data = ziDingYiTjMapper.getCategoryList(tenantCode);;
        return data;
    }

    @Override
    public List<Map<String, Object>> getdataform(Map map) throws Exception{
        String xAxis = String.valueOf(map.get("x"));
        xAxis = xAxis==null?"":xAxis;
        String xAxis_0 = xAxis.split(",")[0];
        String startTime = map.get("startTime") == null ? null: String.valueOf(map.get("startTime"));
        String endTime = map.get("endTime") == null ? null: String.valueOf(map.get("endTime"));
        String dept = map.get("dept") == null ? null: String.valueOf(map.get("dept"));
        String category = map.get("category") == null ? null: String.valueOf(map.get("category"));
        StringBuilder sql = new StringBuilder();
        if("role".equals(xAxis_0)) {
            String userids=xAxis.replace("role,", "");//role,张三,李四,
            String[] useridss=userids.split(",");
            StringBuilder idlist = new StringBuilder();
            for(String id:useridss) {
                //id=id.substring(id.lastIndexOf("#")+1);
                id=id.substring(0,id.lastIndexOf("#"));
                idlist.append("'"+id+"',");
            }
            idlist.setLength(idlist.length()-1);
            String tenantCode = SecurityUtils.getTenantCode();

            sql.append("select a.user_name,count(1) as user_count from "+tenantCode+"_LOGININFOR a inner join sys_user b on a.user_name = b.user_name where a.status = 0");
            sql.append(" and b.user_id IN ("+idlist.toString()+")");
            if(null!=startTime&&!"".equals(startTime)) {
                sql.append(" and to_char(a.ACCESS_TIME,'yyyy-MM-dd') >= '"+startTime+"'");
            }
            if(null!=endTime&&!"".equals(endTime)) {
                sql.append(" and to_char(a.ACCESS_TIME,'yyyy-MM-dd') <= '"+endTime+"'");
            }
            if(null!=dept&&!"".equals(dept)) {
                sql.append(" and a.user_name IN (select user_name from sys_user where tenantcode = '"+tenantCode+"' and DEPT_ID in (" +
                        " select DEPT_ID" +
                        " from sys_dept where TENANTCODE = '"+tenantCode+"'" +
                        " start with DEPT_ID = '"+dept+"'" +
                        " connect by prior DEPT_ID=parent_id" +
                        "))");
            }
            sql.append(" GROUP BY a.user_name");
        }else if("year".equals(xAxis_0)) {
            String year = xAxis.split(",")[1].split("#")[0];
            String lianxu = xAxis.split(",")[1].split("#")[1];
            List<String> list4 = new ArrayList<String>();
            for (int i = 0; i < Integer.parseInt(lianxu); i++) {
                list4.add("'" + (Integer.parseInt(year) + i) + "'");
            }
            String tenantCode = SecurityUtils.getTenantCode();
            sql.append("SELECT  ND AS NAME,");
            sql.append("count (a . da_id ) as jianshu,sum(a.ys) as YESHU,sum (a.ys) as JIANYS, (select count(1) from "+tenantCode+"_YQFOLDER where nd=a.nd )  as juanshu,")
                    .append("(select sum(ys) from "+tenantCode+"_YQDA where da_id in (SELECT da_id FROM "+tenantCode+"_yqda_folder WHERE folder_id IN (SELECT folder_id FROM "+tenantCode+"_YQFOLDER WHERE ND=a.ND )))as JUANYS,")
                    .append("(SELECT COUNT(1) FROM yqdaborrow_item WHERE da_id in (SELECT da_id FROM "+tenantCode+"_YQDA WHERE ND=A.ND)) as JYRC");

            //sql = sql.deleteCharAt(sql.length() - 1);
            sql.append(" FROM  "+tenantCode+"_YQDA a WHERE nd in(").append(list4.toString().replace("[", "").replace("]", "")).append(") ");
            if (null != dept && !"".equals(dept)) {// 部门
                sql.append(" and a.gdsj_id ='").append(dept).append("'");
                /*sql.append(" and a.gdsj_id IN (select DEPT_ID" +
                        " from sys_dept where TENANTCODE = '"+tenantCode+"'" +
                        " start with DEPT_ID = '"+dept+"'" +
                        " connect by prior DEPT_ID=parent_id)");*/
            }
            if (null != category && !"".equals(category)) {
                sql.append(" and a.sub_table_name in(select file_table_name from "+tenantCode+"_CATEGORY where id=").append(category).append(")");
            }

            if (null != startTime && !"".equals(startTime)) {// 开始日期
                sql.append(" and a.create_time >='").append(startTime).append("'");
            }
            if (null != endTime && !"".equals(endTime)) {// 结束日期
                sql.append(" and a.create_time <='").append(endTime).append("'");
            }
            sql.append(" GROUP BY a.nd ORDER BY a.nd");
        }else if("dept".equals(xAxis_0)) {
            String tenantCode = SecurityUtils.getTenantCode();
            sql.append("SELECT DEPT_NAME AS NAME,DEPT_ID,count(b.da_id) as jianshu,sum(b.ys) as YESHU,sum (b.ys) as JIANYS,count(c.folder_id) as juanshu,")
                    .append("(select sum(ys) from "+tenantCode+"_YQDA " +
                            "where da_id in (SELECT da_id FROM "+tenantCode+"_yqda_folder WHERE folder_id IN (SELECT folder_id FROM "+tenantCode+"_YQFOLDER WHERE gdsj_id=a.DEPT_ID ").append("))) as juanys,")
                    .append("(SELECT COUNT(1) FROM yqdaborrow_item WHERE da_id in (SELECT da_id FROM "+tenantCode+"_YQDA WHERE gdsj_id=a.dept_id )) as jyrc");
            //sql = sql.deleteCharAt(sql.length() - 1);
            String x = xAxis.split(",")[0];
            String xx = xAxis.replace(x + ",", "");
            String[] useridss = xx.split(",");
            List<String> idlist = new ArrayList<String>();
            for (String id : useridss) {
                id = id.substring(0, id.lastIndexOf("#"));
                idlist.add(id);
            }

            sql.append(" FROM SYS_DEPT a left join "+tenantCode+"_YQDA b on a.DEPT_ID=gdsj_id left join "+tenantCode+"_YQFOLDER c on a.DEPT_ID=c.gdsj_id " +
                    "WHERE a.DEPT_ID in ("+idlist.toString().replace("[", "").replace("]", "")+")");
            //sql.append(" FROM hdrole a left join hdda b on a.role_id=gdsj_id  left join HDFOLDER c on a.role_id=c.gdsj_id  WHERE a.role_id in(").append(idlist.toString().replace("[", "").replace("]", "")).append(")").append((HD_ACL_ON_OFF && acl != null ? " AND a.ACL=" + acl + " AND b.ACL=" + acl + " AND c.ACL=" + acl : ""));

            if (null != dept && !"".equals(dept)) {// 部门
                sql.append(" and a.dept_id ='"+map.get("dept")+"'");

                /*sql.append(" and a.dept_id IN (select DEPT_ID " +
                        " from sys_dept where TENANTCODE = '"+tenantCode+"'" +
                        " start with DEPT_ID = '"+dept+"'" +
                        " connect by prior DEPT_ID=parent_id)");*/
            }

            if (null != category && !"".equals(category)) {// 门类
                // 先根据门类找到全宗,根据全宗找到部门,这个部门肯定是立档单位
                String unit = ziDingYiTjMapper.getSingle("select dept_id from MANAGE_FONDS where tenantcode = '"+tenantCode+"' and fonds_code=(select fonds_code from "+tenantCode+"_CATEGORY where id=" + category + ")");
                sql.append(" and a.dept_id in(select DEPT_ID from sys_dept where TENANTCODE = '"+tenantCode+"' start with DEPT_ID = '"+unit+"' connect by prior DEPT_ID=parent_id)");
            }
            if (null != startTime && !"".equals(startTime)) {// 开始日期
                sql.append(" and b.create_time >='").append(startTime).append("'");
                sql.append(" and c.create_time >='").append(startTime).append("'");
            }
            if (null != endTime && !"".equals(endTime)) {// 结束日期
                sql.append(" and b.create_time <='").append(endTime).append("'");
                sql.append(" and c.create_time <='").append(endTime).append("'");
            }
            sql.append(" GROUP BY a.dept_name,a.dept_id");
        }else if("category".equals(xAxis_0)) {
            String tenantCode = SecurityUtils.getTenantCode();
            sql.append("SELECT NAME as NAME,count (b.da_id) as jianshu,sum(b.ys) as YESHU,sum (b.ys) as JIANYS, count(c.folder_id)  as juanshu,")
                    .append("(select sum(ys) from "+tenantCode+"_YQDA where da_id in (SELECT da_id FROM "+tenantCode+"_yqda_folder WHERE folder_id IN (SELECT folder_id FROM "+tenantCode+"_YQFOLDER " +
                            "WHERE SUB_TABLE_NAME=a.FOLDER_TABLE_NAME))) as juanys,")
                    .append("(SELECT COUNT(1) FROM yqdaborrow_item WHERE da_id in (SELECT da_id FROM "+tenantCode+"_YQDA WHERE SUB_TABLE_NAME=a.FILE_TABLE_NAME)) as jyrc");
            //sql = sql.deleteCharAt(sql.length() - 1);
            String x = xAxis.split(",")[0];
            String xx = xAxis.replace(x + ",", "");
            String[] useridss = xx.split(",");
            List<String> idlist = new ArrayList<String>();
            for (String id : useridss) {
                id = id.substring(0, id.lastIndexOf("#"));
                idlist.add(id);
            }
            sql.append(" FROM "+tenantCode+"_CATEGORY a left join "+tenantCode+"_YQDA b on a.FILE_TABLE_NAME=b.SUB_TABLE_NAME left join "+tenantCode+"_YQFOLDER c on a.FOLDER_TABLE_NAME=c.SUB_TABLE_NAME WHERE a.id in(")
                    .append(idlist.toString().replace("[", "").replace("]", "")).append(")");
            if (null != dept && !"".equals(dept)) {// 部门
                sql.append(" and a.fonds_code in(select fonds_code from MANAGE_FONDS " +
                        "where dept_id in(select DEPT_ID from sys_dept where TENANTCODE = '"+tenantCode+"' start with DEPT_ID = '"+dept+"' connect by prior DEPT_ID=parent_id)) ");
            }
            String categoryType="";
            if(null!=map.get("category")&&!"".equals(map.get("category"))) {//门类
                sql.append(" and  a.id="+map.get("category").toString());
            }
            if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                sql.append(" and b.create_time >='"+map.get("startTime")+"'");
                sql.append(" and c.create_time >='"+map.get("startTime")+"'");
            }
            if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                sql.append(" and b.create_time <='"+map.get("endTime")+"'");
                sql.append(" and c.create_time <='"+map.get("endTime")+"'");
            }
            sql.append(" GROUP BY a.name,a.file_table_name,a.folder_table_name");
        }
        logger.info(sql.toString());
        return ziDingYiTjMapper.getListResult(sql.toString());
    }

    @Override
    public List<Map<String, Object>> getdatatable(Map map) throws Exception {
        List<Map<String, Object>> list = new ArrayList();
        StringBuffer sql=new StringBuffer();
        String tenantCode = SecurityUtils.getTenantCode();
        if("role".equals(map.get("x").toString().split(",")[0])) {
            String xAxis = String.valueOf(map.get("x"));
            xAxis = xAxis==null?"":xAxis;
            String userids=xAxis.replace("role,", "");//role,张三,李四,
            String[] useridss=userids.split(",");
            StringBuilder idlist = new StringBuilder();
            for(String id:useridss) {
                //id=id.substring(id.lastIndexOf("#")+1);
                id=id.substring(0,id.lastIndexOf("#"));
                idlist.append("'"+id+"',");
            }
            idlist.setLength(idlist.length()-1);
            sql.append("select a.user_name,count(1) as user_count from "+tenantCode+"_LOGININFOR a inner join sys_user b on a.user_name = b.user_name where a.status = 0");
            sql.append(" and b.user_id IN ("+idlist.toString()+")");
            if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {
                sql.append(" and a.ACCESS_TIME >= '"+map.get("startTime")+"'");
            }
            if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {
                sql.append(" and a.ACCESS_TIME <= '"+map.get("endTime")+"'");
            }
            if(null!=map.get("dept")&&!"".equals(map.get("dept"))) {
                sql.append(" and a.user_name IN (select user_name from sys_user where tenantcode = '"+tenantCode+"' DEPT_ID in (" +
                        " select DEPT_ID" +
                        " from sys_dept where TENANTCODE = '"+tenantCode+"'" +
                        " start with DEPT_ID = '"+map.get("dept")+"'" +
                        " connect by prior DEPT_ID=parent_id" +
                        "))");
            }
            sql.append(" GROUP BY a.user_name");
        }else if("year".equals(map.get("x").toString().split(",")[0])) {
            String year=map.get("x").toString().split(",")[1].split("#")[0];
            String lianxu=map.get("x").toString().split(",")[1].split("#")[1];
            List list4=new ArrayList();
            for(int i=0;i<Integer.parseInt(lianxu);i++) {
                list4.add("'"+(Integer.parseInt(year)+i)+"'");
            }
            sql.append("SELECT  ND,");
            String y=map.get("y").toString().split(",")[0];
            String yy=map.get("y").toString().replace(y+",", "");
            String[] ys=yy.split(",");
            for(String id:ys) {
                id=id.substring(0,id.lastIndexOf("#"));
                String ziduan=map.get("y").toString().split(",")[0];
                sql.append("(SELECT count(1) FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND nd=a.nd ");
                if(null!=map.get("category")&&!"".equals(map.get("category"))) {
                    sql.append(" and sub_table_name in(select file_table_name from "+tenantCode+"_CATEGORY where id="+map.get("category").toString()+")");
                }
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JIANSHU\",");
                sql.append("(SELECT count(1) FROM "+tenantCode+"_YQFOLDER WHERE "+ziduan+"='"+id+"' AND nd=a.nd ");
                if(null!=map.get("category")&&!"".equals(map.get("category"))) {
                    sql.append(" and sub_table_name in(select folder_table_name from "+tenantCode+"_CATEGORY where id="+map.get("category").toString()+")");
                }
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JUANSHU\",");
                sql.append("(SELECT CASE WHEN sum(ys) IS NULL THEN 0 ELSE sum(ys) end FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND nd=a.nd ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JIANYS\",");
                sql.append("(SELECT CASE WHEN sum(ys) IS NULL THEN 0 ELSE sum(ys) end FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND nd=a.nd ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"YESHU\",");
                sql.append("(SELECT CASE WHEN sum(ys) IS NULL THEN 0 ELSE sum(ys) end FROM "+tenantCode+"_YQDA WHERE da_id IN " +
                        "(SELECT da_id FROM "+tenantCode+"_yqda_folder WHERE folder_id in(SELECT FOLDER_ID FROM "+tenantCode+"_YQFOLDER WHERE "+ziduan+"='"+id+"' AND nd=a.nd ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")))AS \""+id+"JUANYS\",");
                sql.append("(SELECT count(1) FROM yqdaborrow_item WHERE da_id in(SELECT da_id FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND nd=a.nd) ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and start_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and start_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JYRC\",");
            }
            sql=sql.deleteCharAt(sql.length() - 1);
            sql.append(" FROM "+tenantCode+"_YQDA a WHERE nd in("+list4.toString().replace("[", "").replace("]", "")+") ");
            if(null!=map.get("dept")&&!"".equals(map.get("dept"))) {//部门
                sql.append(" and gdsj_id ='"+map.get("dept")+"'");
            }
            if(null!=map.get("category")&&!"".equals(map.get("category"))) {
                sql.append(" and sub_table_name in(select file_table_name from "+tenantCode+"_CATEGORY where id="+map.get("category").toString()+")");
            }
            sql.append(" GROUP BY nd ORDER BY nd");
        }else if("dept".equals(map.get("x").toString().split(",")[0])) {
            String[] depts=map.get("x").toString().split(",")[1].split(",");
            sql.append("SELECT DEPT_NAME,DEPT_ID,");
            String y=map.get("y").toString().split(",")[0];
            String yy=map.get("y").toString().replace(y+",", "");
            String[] ys=yy.split(",");
            for(String id:ys) {
                id=id.substring(0,id.lastIndexOf("#"));
                String ziduan=map.get("y").toString().split(",")[0];
                sql.append("(SELECT count(1) FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND gdsj_id=a.DEPT_ID ");
                if(null!=map.get("category")&&!"".equals(map.get("category"))) {
                    sql.append(" and sub_table_name in(select file_table_name from "+tenantCode+"_CATEGORY where id="+map.get("category").toString()+")");
                }
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JIANSHU\",");
                sql.append("(SELECT count(1) FROM "+tenantCode+"_YQFOLDER WHERE "+ziduan+"='"+id+"' AND gdsj_id=a.DEPT_ID ");
                if(null!=map.get("category")&&!"".equals(map.get("category"))) {
                    sql.append(" and sub_table_name in(select file_table_name from "+tenantCode+"_CATEGORY where id="+map.get("category").toString()+")");
                }
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JUANSHU\",");
                sql.append("(SELECT sum(ys) FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND gdsj_id=a.DEPT_ID ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JIANYS\",");
                sql.append("(SELECT sum(ys) FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND gdsj_id=a.DEPT_ID ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"YESHU\",");
                sql.append("(SELECT sum(ys) FROM "+tenantCode+"_YQDA WHERE da_id IN (SELECT da_id FROM "+tenantCode+"_yqda_folder " +
                        "WHERE folder_id in(SELECT FOLDER_ID FROM "+tenantCode+"_YQFOLDER WHERE "+ziduan+"='"+id+"' AND gdsj_id=a.DEPT_ID ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")))AS \""+id+"JUANYS\",");
                sql.append("(SELECT count(1) FROM yqdaborrow_item WHERE da_id in(SELECT da_id FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND gdsj_id=a.DEPT_ID) ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and start_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and start_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JYRC\",");
            }
            sql=sql.deleteCharAt(sql.length() - 1);
            String x=map.get("x").toString().split(",")[0];
            String xx=map.get("x").toString().replace(x+",", "");
            String[] useridss=xx.split(",");
            List idlist=new ArrayList();
            for(String id:useridss) {
                id=id.substring(0,id.lastIndexOf("#"));
                idlist.add(id);
            }
            sql.append(" FROM sys_dept a WHERE dept_id in("+idlist.toString().replace("[", "").replace("]", "")+")");

            if(null!=map.get("dept")&&!"".equals(map.get("dept"))) {//部门
                sql.append(" and dept_id ='"+map.get("dept")+"'");
            }

            if(null!=map.get("category")&&!"".equals(map.get("category"))) {//门类
                String unit = ziDingYiTjMapper.getSingle("select dept_id from MANAGE_FONDS where tenantcode = '"+tenantCode+"' and fonds_code=(select fonds_code from "+tenantCode+"_CATEGORY where id=" + map.get("category") + ")");
                sql.append(" and a.dept_id in(select DEPT_ID from sys_dept where TENANTCODE = '"+tenantCode+"' start with DEPT_ID = '"+unit+"' connect by prior DEPT_ID=parent_id)");
            }
            sql.append(" GROUP BY dept_name,dept_id");
        }else if("category".equals(map.get("x").toString().split(",")[0])) {
            String[] cates=map.get("x").toString().split(",")[1].split(",");
            sql.append("SELECT max(NAME) as NAME,max(CODE) as CODE,");
            String y=map.get("y").toString().split(",")[0];
            String yy=map.get("y").toString().replace(y+",", "");
            String[] ys=yy.split(",");
            for(String id:ys) {
                id=id.substring(0,id.lastIndexOf("#"));
                String ziduan=map.get("y").toString().split(",")[0];
                sql.append("(SELECT count(1) FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND sub_table_name=a.file_table_name ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JIANSHU\",");
                sql.append("(SELECT count(1) FROM "+tenantCode+"_YQFOLDER WHERE "+ziduan+"='"+id+"' AND sub_table_name=a.folder_table_name ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JUANSHU\",");
                sql.append("(SELECT sum(ys) FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND sub_table_name=a.file_table_name ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"JIANYS\",");
                sql.append("(SELECT sum(ys) FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND sub_table_name=a.file_table_name ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")AS \""+id+"YESHU\",");
                sql.append("(SELECT sum(ys) FROM "+tenantCode+"_YQDA WHERE da_id IN (SELECT da_id FROM "+tenantCode+"_yqda_folder " +
                        "WHERE folder_id in(SELECT FOLDER_ID FROM "+tenantCode+"_YQFOLDER WHERE "+ziduan+"='"+id+"' AND sub_table_name=a.folder_table_name ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append(")))AS \""+id+"JUANYS\",");
                sql.append("(SELECT count(1) FROM yqdaborrow_item WHERE da_id in(SELECT da_id FROM "+tenantCode+"_YQDA WHERE "+ziduan+"='"+id+"' AND sub_table_name=a.file_table_name ");
                if(null!=map.get("startTime")&&!"".equals(map.get("startTime"))) {//开始日期
                    sql.append(" and create_time >='"+map.get("startTime")+"'");
                }
                if(null!=map.get("endTime")&&!"".equals(map.get("endTime"))) {//结束日期
                    sql.append(" and create_time <='"+map.get("endTime")+"'");
                }
                sql.append("))AS \""+id+"JYRC\",");
            }
            sql=sql.deleteCharAt(sql.length() - 1);
            String x=map.get("x").toString().split(",")[0];
            String xx=map.get("x").toString().replace(x+",", "");
            String[] useridss=xx.split(",");
            List idlist=new ArrayList();
            for(String id:useridss) {
                id=id.substring(0,id.lastIndexOf("#"));
                idlist.add(id);
            }
            sql.append(" FROM "+tenantCode+"_CATEGORY a WHERE a.id in("+idlist.toString().replace("[", "").replace("]", "")+")");
            if(null!=map.get("dept")&&!"".equals(map.get("dept"))) {//部门
                sql.append(" and a.fonds_code in(select fonds_code from MANAGE_FONDS " +
                        "where dept_id in(select DEPT_ID from sys_dept where TENANTCODE = '"+tenantCode+"' start with DEPT_ID = '"+map.get("dept")+"' connect by prior DEPT_ID=parent_id)) ");
            }
            if(null!=map.get("category")&&!"".equals(map.get("category"))) {//门类
                sql.append(" and a.id="+map.get("category").toString());
            }

            sql.append(" GROUP BY a.file_table_name,a.folder_table_name");
        }
        String is_category = ziDingYiTjMapper.getSingle("select count(1) from "+tenantCode+"_CATEGORY;");
        if(!"0".equals(is_category)) {  //门类不为空时查询
            logger.info(sql.toString());
            list=ziDingYiTjMapper.getListResult(sql.toString());
        }
        return list;
    }
}
